import hashlib
import itertools
import logging
import json
import time
from functools import reduce
from operator import or_

from flask import current_app as app, url_for, request_started
from flask_login import current_user, AnonymousUserMixin, UserMixin
from passlib.apps import custom_app_context as pwd_context
from sqlalchemy.exc import DBAPIError
from sqlalchemy.dialects import postgresql

from sqlalchemy_utils import EmailType
from sqlalchemy_utils.models import generic_repr

from redash import redis_connection
from redash.utils import generate_token, utcnow, gen_query_hash

from .base import db, Column, GFKBase, key_type, primary_key
from .mixins import TimestampMixin, BelongsToOrgMixin
from .types import json_cast_property, MutableDict, MutableList

logger = logging.getLogger(__name__)


@generic_repr("id", "code", "name","org_id","items")
class Enums(db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="enums")
    code = Column(db.String(100))
    name = Column(db.String(255))
    is_builtin = Column(db.Boolean(False))
    items = Column(
        MutableList.as_mutable(postgresql.ARRAY(postgresql.JSON)),
        nullable=True,
        server_default=json.dumps({}),
        default=[],
    )

    __tablename__ = "enums"
    __table_args__ = (db.Index("enums_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "name": self.name,
            "is_builtin": self.is_builtin,
        }

    @classmethod
    def get_by_code(cls, code,org):
        return cls.query.filter(cls.org == org,cls.code == code).one()

    @classmethod
    def all(cls,org):
        return cls.query.filter(cls.org == org).order_by(cls.id.asc())


@generic_repr("id", "code", "name")
class OrgUnit(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(db.Integer, db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", backref=db.backref("orgunit", lazy="dynamic"))
    code = Column(db.String(100))
    name = Column(db.String(255))
    shortname = Column(db.String(100), nullable=True)
    is_active = Column(db.Boolean(True), nullable=True)
    orgunit_enums = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(100))), nullable=True
    )

    __tablename__ = "orgunit"
    __table_args__ = (db.Index("orgunit_org_code", "org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "name": self.name,
            "shortname": self.shortname,
            "is_active": self.is_active,
            "orgunit_enums": self.orgunit_enums,
        }

    @classmethod
    def get_by_code(cls, org, code):
        return cls.get_by_org(org).filter(cls.code == code).one()

    @classmethod
    def all(cls, org):
        return cls.get_by_org(org)

    @classmethod
    def by_orgunit_enum(cls, org, orgunit_enum):
        return cls.get_by_org(org).filter(cls.orgunit_enums.any(orgunit_enum))

    @classmethod
    def get_by_org(cls, org):
        return cls.query.filter(cls.org == org)

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def search(cls, base_query, term):
        term = "%{}%".format(term)
        search_filter = cls.name.ilike(term)

        return base_query.filter(search_filter)

    def is_used(self):
        for orgstruct in OrgStruct.get_by_org(self.org):
            if orgstruct.has_orgunit(self.id):
                return True

        return False


@generic_repr("id", "orgstruct_enum")
class OrgStruct(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(db.Integer, db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", backref=db.backref("orgstruct", lazy="dynamic"))
    orgstruct_enum = Column(db.String(100))
    effect_date = Column(db.Date)
    expire_date = Column(db.Date, default=None, nullable=True)
    details = Column(
        MutableList.as_mutable(postgresql.ARRAY(postgresql.JSON)),
        nullable=True,
        server_default=json.dumps({}),
        default=[],
    )

    __tablename__ = "orgstruct"
    __table_args__ = (db.Index("orgstruct_org_orgstruct_effect","org_id", "orgstruct_enum", "effect_date", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.orgstruct_enum,
            self.effect_date,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "effect_date": self.effect_date,
            "expire_date": self.expire_date,
        }

    def to_dict_full(self):
        return {
            "id": self.id,
            "effect_date": self.effect_date,
            "expire_date": self.expire_date,
            "orgstruct_enum": self.orgstruct_enum,
            "details": self.details,
        }

    @classmethod
    def get_by_effect_date(cls, org, orgstruct_enum, effect_date):
        return cls.all(org, orgstruct_enum).filter(cls.effect_date == effect_date).one_or_none()

    @classmethod
    def get_last_by_org_orgstruct_enum(cls, org, orgstruct_enum):
        os = cls.all(org, orgstruct_enum)
        return os.first() if os else None

    @classmethod
    def get_last(cls, os):
        os = cls.all(os.org, os.orgstruct_enum).filter(cls.id < os.id).order_by(cls.id.desc())
        return os.first() if os else None

    @classmethod
    def get_next(cls, os):
        os = cls.all(os.org, os.orgstruct_enum).filter(cls.id > os.id).order_by(cls.id.asc())
        return os.first() if os else None

    @classmethod
    def all(cls, org, orgstruct_enum):
        return cls.get_by_org(org).filter(cls.orgstruct_enum == orgstruct_enum).order_by(cls.effect_date.desc())

    @classmethod
    def get_by_org(cls, org):
        return cls.query.filter(cls.org == org)

    @classmethod
    def get_by_id(cls, _id):
        return cls.query.filter(cls.id == _id).one()

    def is_used(self):
        return False

    def has_orgunit(self, orgunit_id):
        if self.details is None or len(self.details) == 0:
            return False
        else:
            return True if str(self.details).find(": \'" + str(orgunit_id) + "\',") >= 0 else False


@generic_repr("id", "calendar_enum", "title", "timeserialnode_enum", "num", "parent_id")
class TimeStruct(db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="timestruct")
    calendar_enum = Column(db.String(100))
    title = Column(db.String(255))
    timeserialnode_enum = Column(db.String(100))
    num = Column(db.Integer, default=0, nullable=True)
    parent_id = Column(db.Integer, default=0)

    __tablename__ = "timestruct"
    __table_args__ = (db.Index("timestruct_org_calendar_code", "org_id", "calendar_enum", "title", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.title,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "calendar_enum": self.calendar_enum,
            "title": self.title,
            "parent_id": self.parent_id,
        }

    def to_tree(self):
        return {
            "key": self.id,
            "title": self.title,
            "parent_id": self.parent_id,
            "children": [],
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_by_parent_id(cls, parent_id,org):
        return cls.query.filter(cls.org == org,cls.parent_id == parent_id)

    @classmethod
    def get_by_calendar_enum(cls, calendar_enum,org):
        return cls.query.filter(cls.org == org, cls.calendar_enum == calendar_enum).order_by(cls.parent_id, cls.num, cls.id)

    @classmethod
    def delete_by_id(cls, id):
        rs = cls.get_by_parent_id(id)
        for r in rs:
            cls.delete_by_id(r.id)

        timestruct = cls.get_by_id(id)
        db.session.delete(timestruct)


@generic_repr("id", "calendar_enum", "name")
class TimeSerial(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="timeserial")
    num = Column(db.Integer)
    name = Column(db.String(255))
    calendar_enum = Column(db.String(255))
    timestruct_id = Column(db.Integer, db.ForeignKey("timestruct.id"))
    timestruct = db.relationship("TimeStruct", backref=db.backref("timeserial", lazy="dynamic"))
    parent_id = Column(db.Integer, default=0)
    begin_date = Column(db.DateTime(True), nullable=False)
    close_date = Column(db.DateTime(True), nullable=False)
    timeserialstatus_enum = Column(db.String(255), default="draft")

    __tablename__ = "timeserial"
    __table_args__ = (db.Index("timeserial_org_calendar_timestruct_parent_num","org_id", "calendar_enum", "timestruct_id", "parent_id", "num", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.calendar_enum,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "num": self.num,
            "name": self.name,
            "calendar_enum": self.calendar_enum,
            "timestruct_id": self.timestruct_id,
            "parent_id": self.parent_id,
            "begin_date": self.begin_date,
            "close_date": self.close_date,
            "timeserialstatus_enum": self.timeserialstatus_enum,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_by_parent_id(cls, parent_id, timestruct_id,org):
        return cls.query.filter(cls.org == org, cls.parent_id == parent_id, cls.timestruct_id == timestruct_id)

    @classmethod
    def get_by_timestruct_id(cls, timestruct_id):
        return cls.query.filter(cls.timestruct_id == timestruct_id)

    @classmethod
    def get_timeserialtree(cls, _timeserialstatus_enum,_org_id):
        strsql = """select timeserial_tree(:t_enum,:org_id)  as strtree """
        return db.session.execute(strsql, {"t_enum": tuple(_timeserialstatus_enum),"org_id": _org_id}).fetchone()


@generic_repr("id", "query_id", "timeserial_id", "query_hash", "latest_query_data_id", "is_draft", "schedule_failures")
class QueryTimeSerial(db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="query_timeserial")
    query_id = Column(db.Integer, db.ForeignKey("queries.id"))
    qyery = db.relationship("Query")
    timeserial_id = Column(db.Integer, db.ForeignKey("timeserial.id"))
    timeserial = db.relationship(TimeSerial)
    query_text = Column("query", db.Text)
    query_hash = Column(db.String(32))
    latest_query_data_id = Column(
        db.Integer, db.ForeignKey("query_results.id"), nullable=True
    )
    latest_query_data = db.relationship("QueryResult")
    is_draft = Column(db.Boolean, default=True)
    schedule_failures = Column(db.Integer, default=0)

    __tablename__ = "query_timeserial"
    __table_args__ = (db.Index("query_id_org_timeserial_id","query_id", "org_id", "timeserial_id", unique=True),)

    @classmethod
    def get_by_id(cls, _id):
        return cls.query.filter(cls.id == _id).one()

    @classmethod
    def get_by_query_id_and_timeserial_id(cls, query_id, timeserial_id):
        return cls.query.filter(cls.query_id == query_id, cls.timeserial_id == timeserial_id).one()

    @classmethod
    def create_by_query_and_timeserial_id(cls, query, timeserial_id):
        timeserial = TimeSerial.get_by_id(timeserial_id)

        query_text = query.query_text
        type = query.data_source.type
        if query_text is not None:
            if (type == "json" or type == "csv" or type == "excel") and query_text.indexOf("files/") == 0:
                query_text = None  # 文件每期重新上传，回草稿状态
                is_draft = True
            else:
                query_text = query_text.replace("{{begin_date}}", timeserial.begin_date)
                query_text = query_text.replace("{{close_date}}", timeserial.close_date)

        query_timeserial = cls(
            query=query,
            timeserial=timeserial,
            query_text=query_text,
            query_hash=gen_query_hash(query_text) if query_text is not None else None
        )

        db.session.add(query_timeserial)
        db.session.commit()

        return query_timeserial
    
#==========================================列表显示隐藏列=======
@generic_repr("id", "code", "name")
class QueryShowField(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="queryshowfield")
    code = Column(db.String(100))
    name = Column(db.String(255))
    fieldsshow = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )
    fieldsall = Column(
        MutableList.as_mutable(postgresql.JSON),
        nullable=True,
        server_default="{}",
        default={},
    ) #附件
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    userid = Column(db.Integer) #录入人ID

    __tablename__ = "queryshowfield"
    __table_args__ = (db.Index("queryshowfield_org_code","org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "code": self.code,
            "name": self.name,
            "fieldsshow": self.fieldsshow,
            "fieldsall": self.fieldsall,
            "message": self.message,
            "userid": self.userid,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_code(cls, code):
        return cls.query.filter(cls.code == code).one()

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
